﻿using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Text;
using System.Text.RegularExpressions;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using Framework.Data.Core;
using Framework.Data.DataBroker;
using Framework.Data.Exceptions;
using Framework.Data.Security;
using Framework.Data.DataType;

/// <summary>
/// Classe qui permet de persister les entités dans une bd oracle.
/// Elle permet aussi d'extraire les données d'oracle pour les convertir en entités
/// </summary>
/// <remarks></remarks>
/// 
namespace Oracle.DataBroker
{
    public class OracleDataBroker : IDataBroker
    {

        #region "Constantes"
        private const Int32 UNIQUE_CONSTRAINT = 1;
        private const Int32 FOREIGN_CONSTRAINT = 2291;
        private const Int32 INTEGRITY_CONSTRAINT = 2292;
        private const Int32 VALUE_TOO_LARGE = 12899;
        private const Int32 VALUE_NUMERIC_TOO_LARGE = 1438;
        private const Int32 INVALID_NUMBER = 1722;
        private const Int32 NULL_EXCEPTION = 1407;
        private const Int32 NULL_EXCEPTION2 = 1400;
        private const Int32 INSUFFICIENT_PRIVILEGES = 1031;
        private const Int32 INSUFFICIENT_PRIVILEGES2 = 6550;
        private const Int32 INVALIDE_PASSWORD = 1017;
        private const Int32 PASSWORD_EMPTY = 1005;
        private const Int32 DNS_NOT_FOUND = 12560;
        public const string DefaultConnectionString = "Data Source={0};User Id={1};Password={2};";
        #endregion
        

        #region "Membres"
		protected OracleConnection _connection = null;
        protected string _connectionString = null;
        #endregion

        #region "Constructeurs"

        /// <summary>
        /// Constructeur
        /// </summary>
        /// <remarks></remarks>
        public OracleDataBroker(string connectionString)
        {
        	this._connectionString = connectionString;
        }

        #endregion

        #region "Propriétés"
        
        public IDbConnection Connection
        {
        	get{ return this._connection;}
        }

        #endregion

        #region "Méthodes"
        
        public bool OpenConnection()
        {
        	this._connection = new OracleConnection(this._connectionString);
            try
            {
                this._connection.Open();
                return true;
            }
            catch (Exception ex)
            {
                if (ex is OracleException)
                {
                    if ((((OracleException)ex).Number == INVALIDE_PASSWORD) || (((OracleException)ex).Number == PASSWORD_EMPTY))
                    {
                        AppException exception = new AppException("MSG_MOT_DE_PASSE_INCORRECTE", ex);
                        throw exception;
                    }
                    else if ((((OracleException)ex).Number == DNS_NOT_FOUND))
                    {
                        AppException exception = new AppException("MSG_DNS_INCORRECTE", ex);
                        throw exception;
                    }
                }
                throw ex;
            }
        }

        /// <summary>
        /// Fonction qui retourne le prochain id disponible
        /// </summary>
        /// <returns>Int32</returns>
        /// <remarks></remarks>
        public long GetNextID(IDbConnection connection = null)
        {
            long result = -1;
            string selectSQL = "SELECT gcl.GC_SEQUENCE.NextVal  FROM DUAL";
            IDataReader dataReader = null;
            try
            {
                dataReader = this.ExecuteReader(selectSQL, connection);
                if (dataReader != null)
                {
                    dataReader.Read();
                    result = Convert.ToInt64(dataReader.GetDecimal(0));
                }

                return result;
            }
            finally
            {
                if (dataReader != null && !dataReader.IsClosed)
                {
                    dataReader.Close();
                    dataReader.Dispose();
                }
            }
        }

        /// <summary>
        /// Retourne la liste complète de ce type d'entité
        /// </summary>
        /// <param name="entityType">Type de l'entité</param>
        /// <param name="filters">Filtres</param>
        /// <returns>IEntityCollection</returns>
        /// <remarks></remarks>
        public IEntityCollection<object, IBaseEntity> GetEntityList(System.Type entityType, List<string> filters, string sortOrder, IDbConnection connection = null, Int32 fetchRowCount = 250, Int32 rowsPerPage = 0, Int32 pageIndex = 0)
        {
            IBaseEntity entity = (IBaseEntity)Activator.CreateInstance(entityType, new object[] { connection });
            string selectSql = this.BuildSelectQuery(entity, filters, sortOrder, rowsPerPage, pageIndex);

            return this.GetEntityList(entityType, selectSql, connection, fetchRowCount);
        }

        /// <summary>
        /// Retourne la liste complète de ce type d'entité
        /// </summary>
        /// <param name="entityType">Type de l'entité</param>
        /// <param name="sql">Select sql</param>
        /// <returns>IEntityCollection</returns>
        /// <remarks></remarks>
        public IEntityCollection<object, IBaseEntity> GetEntityList(System.Type entityType, string sql, IDbConnection connection = null, Int32 fetchRowCount = 250)
        {
            IBaseEntity entity = (IBaseEntity)Activator.CreateInstance(entityType, new object[] { connection });
            IDataReader dataReader = this.ExecuteReader(sql, connection, fetchRowCount);
            EntityCollection<object, IBaseEntity> collection = new EntityCollection<object, IBaseEntity>(entityType);
            try
            {
                if (dataReader != null)
                {
                    while (dataReader.Read())
                    {
                        entity = (IBaseEntity)Activator.CreateInstance(entityType, new object[] { connection });
                        entity.InitializeData(dataReader);
                        collection.Add(entity.HashCode, entity);
                    }
                }
                return collection;
            }
            finally
            {
                if (dataReader != null && !dataReader.IsClosed)
                {
                    dataReader.Close();
                    dataReader.Dispose();
                }
            }
        }

        /// <summary>
        /// Retourne un entité
        /// </summary>
        /// <param name="entityType">Type de l'entité</param>
        /// <param name="id">Id</param>
        /// <returns>IBaseEntity</returns>
        /// <remarks></remarks>
        public IBaseEntity GetEntity(System.Type entityType, long id, IDbConnection connection = null)
        {
            IBaseEntity entity = null;
            OracleDataReader dataReader = null;
            List<string> filters = new List<string>();
            string sql = null;

            try
            {
                entity = (IBaseEntity)Activator.CreateInstance(entityType, new object[] { connection });
                //On crée un filtre sur le id
                filters.Add(" AND " + ((IEntityField)entity.GetPrimaryKeyField()).TableName + "." + ((IEntityField)entity.GetPrimaryKeyField()).DatabaseFieldName + " = " + id.ToString());

                //On crée le sql
                sql = this.BuildSelectQuery(entity, filters, null);
                //On exécute le sql
                dataReader = (OracleDataReader)this.ExecuteReader(sql, connection);
                if (dataReader != null && dataReader.HasRows)
                {
                    //Crée l'entité
                    dataReader.Read();

                    entity = (IBaseEntity)Activator.CreateInstance(entityType, new object[] { connection });
                    entity.InitializeData(dataReader);
                }
                else
                {
                    //L'enregistrement n'a pas été trouvé. On lance une exception.
                    entity = null;
                }

                return entity;
            }
            finally
            {
                if (dataReader != null && !dataReader.IsClosed)
                {
                    dataReader.Close();
                    dataReader.Dispose();
                }
            }
        }

        /// <summary>
        /// Retourne la liste complète de ce type d'entité
        /// </summary>
        /// <param name="entityType">Type de l'entité</param>
        /// <param name="filters">Filtres</param>
        /// <returns>IEntityCollection</returns>
        /// <remarks></remarks>
        public IEntityCollection<object, IBaseEntity> GetAttachedEntityList(System.Type entityType, IBaseEntity parentEntity, string linkSchemaName, string linkTableName, List<string> filters, string sortOrder, IDbConnection connection = null, Int32 fetchRowCount = 250, Int32 rowsPerPage = 0, Int32 pageIndex = 0)
        {
            IBaseEntity entity = null;
            string selectSql = null;
            IDataReader dataReader = null;
            EntityCollection<object, IBaseEntity> collection = new EntityCollection<object, IBaseEntity>(entityType);
            AttachedEntity attachedEntity = null;
            try
            {
                entity = (IBaseEntity)Activator.CreateInstance(entityType, new object[] { connection });
                selectSql = this.BuildSelectAttachQuery(entity, parentEntity, linkSchemaName, linkTableName, filters, sortOrder, rowsPerPage, pageIndex);
                dataReader = this.ExecuteReader(selectSql, connection, fetchRowCount);

                if (dataReader != null)
                {
                    while (dataReader.Read())
                    {
                        entity = (IBaseEntity)Activator.CreateInstance(entityType, new object[] { connection });
                        attachedEntity = new AttachedEntity(entity);
                        attachedEntity.InitializeData(dataReader);
                        attachedEntity.AcceptChanges();
                        collection.Add(attachedEntity.GetPrimaryKeyField().OriginalValue, attachedEntity);
                    }
                }
                return collection;
            }
            finally
            {
                if (dataReader != null && !dataReader.IsClosed)
                {
                    dataReader.Close();
                    dataReader.Dispose();
                }
            }
        }

        /// <summary>
        /// Fonction qui charge un champ CLob dans une string
        /// </summary>
        /// <param name="field">Champ à initialiser</param>
        /// <returns>String</returns>
        /// <remarks></remarks>
        public string ReadClobField(EntityField field, IDbConnection connection = null)
        {
            OracleClob lob = default(OracleClob);
            OracleDataReader reader = null;
            string sql = null;
            string result = "";
            string tableName = null;
            EntityField primaryKeyField = (EntityField)field.Entity.GetPrimaryKeyField();

            try
            {
                tableName = field.Schema + "." + field.TableName;
                sql = "SELECT {0} FROM {1} WHERE {2} = {3}";
                sql = string.Format(sql, field.DatabaseFieldName, tableName, field.TableName + "." + primaryKeyField.DatabaseFieldName, primaryKeyField.Value);
                reader = (OracleDataReader)this.ExecuteReader(sql, connection);
                if (reader != null && reader.Read())
                {
                    if (!object.ReferenceEquals(reader[0], System.DBNull.Value))
                    {
                        lob = reader.GetOracleClob(0);
                        result = lob.Value.ToString();
                    }
                }
            }
            finally
            {
                if ((reader != null) && (!reader.IsClosed))
                {
                    reader.Close();
                    reader.Dispose();
                }
            }

            return result;
        }

        /// <summary>
        /// Fonction qui crée le SELECT à partir d'une entité
        /// </summary>
        /// <param name="entity">L'entité utilisé pour construire le sql</param>
        /// <param name="filters">Filtre pour construire la clause WHERE</param>
        /// <param name="sortOrder">String qui va être utilisé pour le ORDER BY</param>
        /// <param name="rowsPerPage">Nombre d'enregistrement à retourner par page</param>
        /// <param name="pageIndex">Index de la page à retournée. Exemple: si rowsPerPage = 250 et que pageIndex = 2, le sql va retourner les enregistrements entre la position 251 et 500.</param>
        /// <returns>String</returns>
        /// <remarks></remarks>
        private string BuildSelectQuery(IBaseEntity entity, List<string> filters, string sortOrder, Int32 rowsPerPage = 0, Int32 pageIndex = 0)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ");

            //Extrait les noms de colonnes et de tables
            List<string> fields = new List<string>();
            List<string> itemsFields = new List<string>();
            List<string> tables = new List<string>();
            List<string> whereList = new List<string>();
            string foreignTableName = string.Empty;
            JoinInfo joinTmp;

            foreach (IField field in entity.Fields.Values)
            {
                //On ne sélectionne pas les champs Clob
                if ((!object.ReferenceEquals(field.FieldValueType, typeof(Clob))))
                {
                    if (field is IForeignFieldMultiTable)
                    {
                        foreach (JoinInfo join in ((IForeignFieldMultiTable)field).Joins)
                        {
                            foreignTableName = join.RightInfo.TableName.ToUpper();
                            if (!tables.Contains(join.RightInfo.Schema.ToUpper() + "." + join.RightInfo.TableName.ToUpper() + " " + foreignTableName.ToUpper()))
                            {
                                tables.Add(join.RightInfo.Schema.ToUpper() + "." + join.RightInfo.TableName.ToUpper() + " " + foreignTableName.ToUpper());
                                //Ajoute un union entre les tables
                                this.AddForeignWhereClause(join, ref whereList);
                            }
                            else
                            {
                                Int16 i = 1;
                                while (tables.Contains(join.RightInfo.Schema.ToUpper() + "." + join.RightInfo.TableName.ToUpper() + " " + foreignTableName.ToUpper() + i.ToString()))
                                {
                                    i += 1;
                                }
                                foreignTableName = join.RightInfo.TableName.ToUpper() + i.ToString();
                                tables.Add(join.RightInfo.Schema.ToUpper() + "." + join.RightInfo.TableName.ToUpper() + " " + foreignTableName.ToUpper());
                                //Ajoute un union entre les tables
                                joinTmp = join;
                                joinTmp.RightInfo.TableName = foreignTableName;
                                this.AddForeignWhereClause(joinTmp, ref whereList);
                            }
                        }

                        fields.Add(foreignTableName.ToUpper() + "." + ((IForeignFieldMultiTable)field).DatabaseFieldName.ToUpper() + " " + ((IForeignFieldMultiTable)field).Name.ToUpper());
                        itemsFields.Add(((IForeignFieldMultiTable)field).Name.ToUpper());
                    }
                    else
                    {
                        if (field is IForeignField)
                        {
                            foreignTableName = ((IForeignField)field).TableName;
                            if (!tables.Contains(((IForeignField)field).Schema + "." + ((IForeignField)field).TableName + " " + foreignTableName))
                            {
                                tables.Add(((IForeignField)field).Schema + "." + ((IForeignField)field).TableName + " " + foreignTableName);
                                //Ajoute un union entre les tables
                                this.AddForeignWhereClause(foreignTableName, (IEntityField)field, ((IEntityField)entity.GetPrimaryKeyField()).TableName, ref whereList);
                            }
                            else
                            {
                                Int16 i = 1;
                                while (tables.Contains(((IForeignField)field).Schema + "." + ((IForeignField)field).TableName + " " + foreignTableName + i.ToString()))
                                {
                                    i += 1;
                                }
                                foreignTableName = ((IForeignField)field).TableName + i.ToString();
                                tables.Add(((IForeignField)field).Schema + "." + ((IForeignField)field).TableName + " " + foreignTableName);
                            }

                            fields.Add(foreignTableName + "." + ((IForeignField)field).DatabaseFieldName + " " + ((IForeignField)field).Name);
                            itemsFields.Add(((IForeignField)field).Name);
                            this.AddForeignWhereClause(foreignTableName, (IEntityField)field, ((IEntityField)entity.GetPrimaryKeyField()).TableName, ref whereList);
                        }
                        else
                        {
                            //On traite les champs de type IEntityField
                            if (field is IEntityField)
                            {
                                fields.Add(((IEntityField)field).TableName + "." + ((IEntityField)field).DatabaseFieldName);
                                itemsFields.Add(((IEntityField)field).DatabaseFieldName);
                                if (!tables.Contains(((IEntityField)field).Schema + "." + ((IEntityField)field).TableName + " " + ((IEntityField)field).TableName))
                                {
                                    tables.Add(((IEntityField)field).Schema + "." + ((IEntityField)field).TableName + " " + ((IEntityField)field).TableName);
                                    //Ajoute un union entre les tables
                                    this.AddWhereClause((IEntityField)field, ((IEntityField)entity.GetPrimaryKeyField()).TableName, ref whereList);
                                }
                            }
                            else
                            {
                                if (field is ISqlExpressionField)
                                {
                                    fields.Add(((ISqlExpressionField)field).SqlExpression + " " + field.Name);
                                    itemsFields.Add(field.Name);
                                }
                            }
                        }
                    }
                }
            }

            if (!string.IsNullOrEmpty(sortOrder) && rowsPerPage > 0 && pageIndex > 0)
            {
                //Ajoute les colonnes au select
                sb.Append(string.Join(",", itemsFields.ToArray()));
                //From
                sb.Append(" FROM ( SELECT ").Append(string.Join(",", fields.ToArray()));
                sb.Append(" , row_number() OVER ( ").Append(" ORDER BY ").Append(sortOrder).Append(" ) r");
                sb.Append(" FROM ").Append(string.Join(",", tables.ToArray()));
                //Ajoute le Where si nécéssaire
                if (whereList.Count > 0)
                {
                    sb.Append(" WHERE ").Append(string.Join(" AND ", whereList.ToArray()));
                }
                //Ajoute les filtres
                if (filters.Count > 0)
                {
                    sb.Append(string.Join(" ", filters.ToArray()));
                }
                sb.Append(" ) items ");
                sb.Append(" WHERE r BETWEEN " + ((rowsPerPage * pageIndex) - (rowsPerPage - 1)).ToString() + " AND " + (rowsPerPage * pageIndex).ToString());
            }
            else
            {
                //Ajoute les colonnes au select
                sb.Append(string.Join(",", fields.ToArray()));
                //From
                sb.Append(" FROM ").Append(string.Join(",", tables.ToArray()));
                //Ajoute le Where si nécéssaire
                if (whereList.Count > 0)
                {
                    sb.Append(" WHERE ").Append(string.Join(" AND ", whereList.ToArray()));
                }
                //Ajoute les filtres
                if (filters.Count > 0)
                {
                    sb.Append(string.Join(" ", filters.ToArray()));
                }
                //Ajoute le tri
                if ((sortOrder != null) && (sortOrder != string.Empty))
                {
                    sb.Append(" ORDER BY ").Append(sortOrder);
                }
            }


            return sb.ToString();
        }

        /// <summary>
        /// Fonction qui crée le SELECT à partir d'une entité
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        private string BuildSelectAttachQuery(IBaseEntity entity, IBaseEntity parentEntity, string linkSchemaName, string linkTableName, List<string> filters, string sortOrder, Int32 rowsPerPage = 0, Int32 pageIndex = 0)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ");

            //Extrait les noms de colonnes et de tables
            List<string> fields = new List<string>();
            List<string> itemsFields = new List<string>();
            List<string> tables = new List<string>();
            List<string> whereList = new List<string>();
            string foreignTableName = string.Empty;
            JoinInfo joinTmp;

            foreach (IField field in entity.Fields.Values)
            {
                //On ne sélectionne pas les champs Clob
                if ((!object.ReferenceEquals(field.FieldValueType, typeof(Clob))))
                {
                    if (field is IForeignFieldMultiTable)
                    {
                        foreach (JoinInfo join in ((IForeignFieldMultiTable)field).Joins)
                        {
                            foreignTableName = join.RightInfo.TableName.ToUpper();
                            if (!tables.Contains(join.RightInfo.Schema.ToUpper() + "." + join.RightInfo.TableName.ToUpper() + " " + foreignTableName.ToUpper()))
                            {
                                tables.Add(join.RightInfo.Schema.ToUpper() + "." + join.RightInfo.TableName.ToUpper() + " " + foreignTableName.ToUpper());
                                //Ajoute un union entre les tables
                                this.AddForeignWhereClause(join, ref whereList);
                            }
                            else
                            {
                                Int16 i = 1;
                                while (tables.Contains(join.RightInfo.Schema.ToUpper() + "." + join.RightInfo.TableName.ToUpper() + " " + foreignTableName.ToUpper() + i.ToString()))
                                {
                                    i += 1;
                                }
                                foreignTableName = join.RightInfo.TableName.ToUpper() + i.ToString();
                                tables.Add(join.RightInfo.Schema.ToUpper() + "." + join.RightInfo.TableName.ToUpper() + " " + foreignTableName.ToUpper());
                                //Ajoute un union entre les tables
                                joinTmp = join;
                                joinTmp.RightInfo.TableName = foreignTableName;
                                this.AddForeignWhereClause(joinTmp, ref whereList);
                            }
                        }

                        fields.Add(foreignTableName.ToUpper() + "." + ((IForeignFieldMultiTable)field).DatabaseFieldName.ToUpper() + " " + ((IForeignFieldMultiTable)field).Name.ToUpper());
                        itemsFields.Add(((IForeignFieldMultiTable)field).Name.ToUpper());
                    }
                    else
                    {
                        if (field is IForeignField)
                        {
                            foreignTableName = ((IForeignField)field).TableName;
                            if (!tables.Contains(((IForeignField)field).Schema + "." + foreignTableName + " " + foreignTableName))
                            {
                                tables.Add(((IForeignField)field).Schema + "." + foreignTableName + " " + foreignTableName);
                                //Ajoute un union entre les tables
                                this.AddForeignWhereClause(foreignTableName, (IEntityField)field, ((IEntityField)entity.GetPrimaryKeyField()).TableName, ref whereList);
                            }
                            else
                            {
                                Int16 i = 1;
                                while (tables.Contains(((IForeignField)field).Schema + "." + ((IForeignField)field).TableName + " " + ((IForeignField)field).TableName + i.ToString()))
                                {
                                    i += 1;
                                }
                                foreignTableName = ((IForeignField)field).TableName + i.ToString();
                                tables.Add(((IForeignField)field).Schema + "." + ((IForeignField)field).TableName + " " + ((IForeignField)field).TableName + i.ToString());
                            }
                            fields.Add(foreignTableName + "." + ((IForeignField)field).DatabaseFieldName + " " + ((IForeignField)field).Name);
                            itemsFields.Add(((IForeignField)field).Name);
                            this.AddForeignWhereClause(foreignTableName, (IEntityField)field, ((IEntityField)entity.GetPrimaryKeyField()).TableName, ref whereList);
                        }
                        else
                        {
                            //On traite les champs de type IEntityField
                            if (field is IEntityField)
                            {
                                fields.Add(((IEntityField)field).TableName + "." + ((IEntityField)field).DatabaseFieldName);
                                itemsFields.Add(((IEntityField)field).DatabaseFieldName);
                                if (!tables.Contains(((IEntityField)field).Schema + "." + ((IEntityField)field).TableName + " " + ((IEntityField)field).TableName))
                                {
                                    tables.Add(((IEntityField)field).Schema + "." + ((IEntityField)field).TableName + " " + ((IEntityField)field).TableName);
                                    //Ajoute un union entre les tables
                                    this.AddWhereClause((IEntityField)field, ((IEntityField)entity.GetPrimaryKeyField()).TableName, ref whereList);
                                }
                            }
                        }
                    }
                }
            }

            tables.Add(((IEntityField)parentEntity.GetPrimaryKeyField()).Schema + "." + ((IEntityField)parentEntity.GetPrimaryKeyField()).TableName + " " + ((IEntityField)parentEntity.GetPrimaryKeyField()).TableName);
            tables.Add(linkSchemaName + "." + linkTableName + " " + linkTableName);
            this.AddWhereClause((IEntityField)entity.GetPrimaryKeyField(), linkTableName, ref whereList);

            if (!string.IsNullOrEmpty(sortOrder) && rowsPerPage > 0 && pageIndex > 0)
            {
                //Ajoute les colonnes au select
                sb.Append(string.Join(",", itemsFields.ToArray()));
                //From
                sb.Append(" FROM ( SELECT ").Append(string.Join(",", fields.ToArray()));
                sb.Append(" , row_number() OVER ( ").Append(" ORDER BY ").Append(sortOrder).Append(" ) r");
                sb.Append(" FROM ").Append(string.Join(",", tables.ToArray()));
                //Ajoute le Where si nécéssaire
                if (whereList.Count > 0)
                {
                    sb.Append(" WHERE ").Append(string.Join(" AND ", whereList.ToArray()));
                }
                //Ajoute les filtres
                if (filters.Count > 0)
                {
                    sb.Append(string.Join(" ", filters.ToArray()));
                }
                sb.Append(" ) items ");
                sb.Append(" WHERE r BETWEEN " + ((rowsPerPage * pageIndex) - (rowsPerPage - 1)).ToString() + " AND " + (rowsPerPage * pageIndex).ToString());

            }
            else
            {
                //Ajoute les colonnes au select
                sb.Append(string.Join(",", fields.ToArray()));
                //From
                sb.Append(" FROM ").Append(string.Join(",", tables.ToArray()));
                //Ajoute le Where si nécéssaire
                if (whereList.Count > 0)
                {
                    sb.Append(" WHERE ").Append(string.Join(" AND ", whereList.ToArray()));
                }
                //Ajoute les filtres
                if (filters.Count > 0)
                {
                    sb.Append(string.Join(" ", filters.ToArray()));
                }
                //Ajoute le tri
                if ((sortOrder != null) && (sortOrder != string.Empty))
                {
                    sb.Append(" ORDER BY ").Append(sortOrder);
                }
            }

            return sb.ToString();
        }

        /// <summary>
        /// Procédure qui crée un union entre 2 tables si nécéssaire
        /// </summary>
        /// <param name="field">champ de l'entité</param>
        /// <param name="primaryTableName">Nom de la table principal</param>
        /// <param name="whereList">Liste des Unions</param>
        /// <remarks></remarks>
        private void AddWhereClause(IEntityField field, string primaryTableName, ref List<string> whereList)
        {
            StringBuilder whereClause = new StringBuilder();
            if (field is IForeignField)
            {
                whereClause.Append(primaryTableName).Append(".").Append(field.DatabaseFieldName).Append(" = ");
                whereClause.Append(((IForeignField)field).TableName).Append(".").Append(((IForeignField)field).DatabaseFieldName).Append("(+)");
            }
            else
            {
                whereClause.Append(primaryTableName).Append(".").Append(field.DatabaseFieldName).Append(" = ");
                whereClause.Append(field.TableName).Append(".").Append(field.DatabaseFieldName);
            }

            if (!(whereList.Contains(whereClause.ToString())))
            {
                whereList.Add(whereClause.ToString());
            }

        }

        /// <summary>
        /// Procédure qui crée un union entre 2 tables si nécéssaire
        /// </summary>
        /// <param name="foreignTableName">Nom de la table foreign</param>
        /// <param name="field">Champ</param>
        /// <param name="primaryTableName">Nom de la table source</param>
        /// <param name="whereList">Liste qui contient les wheres clause</param>
        /// <remarks></remarks>
        private void AddForeignWhereClause(string foreignTableName, IEntityField field, string primaryTableName, ref List<string> whereList)
        {
            StringBuilder whereClause = new StringBuilder();
            if (field is IForeignField)
            {
                whereClause.Append(primaryTableName).Append(".").Append(((IForeignField)field).PrimaryKeyFieldName).Append(" = ");
                whereClause.Append(foreignTableName).Append(".").Append(((IForeignField)field).ForeignPrimaryKeyFieldName).Append("(+)");
            }

            if (!(whereList.Contains(whereClause.ToString())))
            {
                whereList.Add(whereClause.ToString());
            }
        }

        /// <summary>
        /// Procédure qui crée un union entre 2 tables si nécéssaire
        /// </summary>
        /// <remarks></remarks>
        private void AddForeignWhereClause(JoinInfo @join, ref List<string> whereList)
        {
            StringBuilder whereClause = new StringBuilder();
            whereClause.Append(@join.LeftInfo.TableName.ToUpper()).Append(".").Append(@join.LeftInfo.FieldName.ToUpper()).Append(" = ");
            whereClause.Append(@join.RightInfo.TableName.ToUpper()).Append(".").Append(@join.RightInfo.FieldName.ToUpper()).Append("(+)");

            if (!(whereList.Contains(whereClause.ToString())))
            {
                whereList.Add(whereClause.ToString());
            }
        }

        /// <summary>
        /// Fonction de sauvegarde de l'entité
        /// </summary>
        /// <param name="entity">L'entité à sauvegarder</param>
        /// <returns>boolean</returns>
        /// <remarks></remarks>
        public bool Save(IBaseEntity entity, IDbTransaction transaction, IDbConnection connection = null)
        {
            bool result = true;
            if ((!entity.IsNew) && (!entity.MarkAsDeleted))
            {
                result = this.Update(entity, connection);
                if (result)
                {
                    result = this.SaveChildrenContainers(entity, transaction, connection);
                }
                if (result)
                {
                    //Sauvegarde ses attaches
                    result = this.SaveAttachedContainers(entity, transaction, connection);
                }
            }
            else if ((!entity.MarkAsDeleted))
            {
                //Sauvegarde l'entité
                result = this.Insert(entity, connection);
                if (result)
                {
                    //Sauvegarde ses enfants
                    result = this.SaveChildrenContainers(entity, transaction, connection);
                }
                if (result)
                {
                    //Sauvegarde ses attaches
                    result = this.SaveAttachedContainers(entity, transaction, connection);
                }
                //MarkAsDeleted = true
            }
            else
            {
                result = this.Delete(entity, connection);
            }
            return result;
        }

        /// <summary>
        /// Fonction qui permet de sauvegarder les enfants d'un entité dans la bd
        /// </summary>
        /// <param name="entity">L'entité à sauvegarder</param>
        /// <param name="transaction">La transaction dans laquel l'opération doit s'effectuer</param>
        /// <returns>Booelan</returns>
        /// <remarks></remarks>
        private bool SaveChildrenContainers(IBaseEntity entity, IDbTransaction transaction, IDbConnection connection = null)
        {
            bool result = true;
            //On sauvegarde les enfants
            foreach (ChildrenContainer children in entity.ChildrenContainers.Values)
            {
                foreach (IBaseEntity childrenEntity in children.EntityCollection.Values)
                {
                    if (childrenEntity.IsDirty())
                    {
                        result = result & childrenEntity.Save(transaction, connection);
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// Fonction qui permet de sauvegarder les enfants d'un entité dans la bd
        /// </summary>
        /// <param name="entity">L'entité à sauvegarder</param>
        /// <param name="transaction">La transaction dans laquel l'opération doit s'effectuer</param>
        /// <returns>Booelan</returns>
        /// <remarks></remarks>
        private bool SaveAttachedContainers(IBaseEntity entity, IDbTransaction transaction, IDbConnection connection = null)
        {
            bool result = true;
            //On sauvegarde les enfants
            foreach (AttachedContainer container in entity.AttachedContainers.Values)
            {
                foreach (IBaseEntity attachedEntity in container.EntityCollection.Values)
                {
                    if (attachedEntity.IsNew)
                    {
                        result = result & this.InsertAttachedEntity(attachedEntity, container.Parent, container.LinkShemaName, container.LinkTableName, connection);
                    }
                    else if (attachedEntity.MarkAsDeleted)
                    {
                        result = result & this.DeleteAttachedEntity(attachedEntity, container.Parent, container.LinkShemaName, container.LinkTableName, connection);
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// Fonction qui insert un entité attaché dans la bd
        /// </summary>
        /// <param name="attachedEntity">L'entité à attacher</param>
        /// <param name="parent">Parent</param>
        /// <param name="linkTableName">Nom de la table de lien</param>
        /// <returns>Boolean</returns>
        /// <remarks></remarks>
        private bool InsertAttachedEntity(IBaseEntity attachedEntity, IBaseEntity parent, string linkSchemaName, string linkTableName, IDbConnection connection = null)
        {
            int result = 0;
            string insertSql = this.BuildInsertAttachedQuery(attachedEntity, parent, linkSchemaName, linkTableName);

            try
            {
                result = this.ExecuteSQL(insertSql, connection);
            }
            catch (OracleException ex)
            {
                this.HandleDBException(ex, attachedEntity);
            }

            return (result != 0);
        }

        /// <summary>
        /// Function qui insert un entité dans la bd
        /// </summary>
        /// <param name="entity">L'entité à insérer dans la bd</param>
        /// <returns>Boolean</returns>
        /// <remarks></remarks>
        private bool Insert(IBaseEntity entity, IDbConnection connection = null)
        {
            int result = 0;
            string insertSql = this.BuildInsertQuery(entity, (connection == null));
            try
            {
                result = this.ExecuteSQL(insertSql, connection);
            }
            catch (OracleException ex)
            {
                this.HandleDBException(ex, entity);
            }

            return (result != 0);
        }

        /// <summary>
        /// Procédure d'update d'un entité
        /// </summary>
        /// <param name="entity">L'entité à updater</param>
        /// <returns>boolean</returns>
        /// <remarks></remarks>
        private bool Update(IBaseEntity entity, IDbConnection connection = null)
        {
            int result = 0;
            AppException exception = default(AppException);

            if (CanUpdate(entity,  connection))
            {
                string updateSql = this.BuildUpdateQuery(entity, (connection == null));
                try
                {
                    result = this.ExecuteSQL(updateSql, connection);
                }
                catch (OracleException ex)
                {
                    this.HandleDBException(ex, entity);
                }

                return (result != 0);
            }
            else
            {
                exception = new AppException("MSG_ENREGISTREMENT_BD_DIFFERENT");
                throw exception;
            }
        }

        /// <summary>
        /// Fonction qui vérifie si l'enregistrement dans la bd n'a pas été modifié entre le moment
        /// que noua l'avons lu et le moment où nous voulons le sauvegarder
        /// </summary>
        /// <param name="entity">L'entité à vérifier</param>
        /// <returns>Boolean</returns>
        /// <remarks></remarks>
        protected bool CanUpdate(IBaseEntity entity, IDbConnection connection = null)
        {
            //On crée une nouvelle connection
            if (connection == null)
            {
               connection = this.Connection;
            }
            else
            {
                //En web on ne peut pas faire cette vérification
                return true;
            }

            //On crée le sql
            string sql = "SELECT DATE_MAINTENANCE FROM {0} WHERE {1}";
            string tableName = ((IEntityField)entity.GetPrimaryKeyField()).Schema + "." + ((IEntityField)entity.GetPrimaryKeyField()).TableName;
            string condition = ((IEntityField)entity.GetPrimaryKeyField()).DatabaseFieldName + " = " + entity.GetPrimaryKeyField().Value.ToString();
            sql = string.Format(sql, tableName, condition);

            OracleDataReader reader = null;
            bool result = false;

            try
            {
                //On ouvre la connection
                connection.Open();

                //Exécute Set Role pour assigner les rôles oracle à la session de l'utilisateur
                OracleCommand command = new OracleCommand("GCL.SetRole", (OracleConnection)connection);
                command.CommandType = CommandType.StoredProcedure;
                command.ExecuteNonQuery();

                //On exécute le sql
                reader = (OracleDataReader)this.ExecuteReader(sql, connection);
                if (reader != null)
                {
                    reader.Read();
                    if (reader.HasRows)
                    {
                        DateTime dateMaintenance = default(DateTime);
                        dateMaintenance = reader.GetDateTime(0);
                        result = dateMaintenance.Ticks == ((DateTime)entity.GetField("DATE_MAINTENANCE").OriginalValue).Ticks;
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                    reader.Dispose();
                }
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                    connection.Dispose();
                    connection = null;
                }
            }
            return result;
        }

        /// <summary>
        /// Fonction qui extrait le nom de la colonne foreign du message d'erreur oracle
        /// </summary>
        /// <param name="exceptionMessage">Message d'erreur Oracle</param>
        /// <returns>String</returns>
        /// <remarks></remarks>
        private string ExtractFieldNameFromException(string exceptionMessage, string pattern)
        {
            Match m = null;
            Regex regx = new Regex(pattern);
            m = regx.Match(exceptionMessage);
            if (m.Success)
            {
                m = Regex.Match(m.Value, "\\w+");
                if (m.Success)
                {
                    return m.Value;
                }
                else
                {
                    return string.Empty;
                }
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// Function qui supprime un entité
        /// </summary>
        /// <param name="entity">L'entité à supprimer</param>
        /// <returns>Boolean</returns>
        /// <remarks></remarks>
        public bool Delete(IBaseEntity entity, IDbConnection connection = null)
        {
            int result = 0;
            string deleteSql = this.BuildDeleteQuery(entity);
            result = this.ExecuteSQL(deleteSql, connection);

            return (true);
        }

        /// <summary>
        /// Fonction qui supprimer un attachement
        /// </summary>
        /// <param name="entity">Entité à supprimer</param>
        /// <param name="parent">Parent</param>
        /// <param name="linkTableName">Nom de la table de lien</param>
        /// <returns>Boolean</returns>
        /// <remarks></remarks>
        public bool DeleteAttachedEntity(IBaseEntity entity, IBaseEntity parent, string linkSchemaName, string linkTableName, IDbConnection connection = null)
        {
            int result = 0;
            string deleteSql = this.BuildDeleteAttachedQuery(entity, parent, linkSchemaName, linkTableName);
            result = this.ExecuteSQL(deleteSql, connection);

            return (true);
        }

        /// <summary>
        /// Procédure de construction de la requête UPDATE
        /// </summary>
        /// <param name="entity">L'entité à updater</param>
        /// <returns>boolean</returns>
        /// <remarks></remarks>
        private string BuildInsertQuery(IBaseEntity entity, bool updateSystemField)
        {
            string tableName = ((IEntityField)entity.GetPrimaryKeyField()).Schema + "." + ((IEntityField)entity.GetPrimaryKeyField()).TableName;
            StringBuilder sb = new StringBuilder();
            //Création du Insert
            sb.Append("INSERT INTO ").Append(tableName).Append(" (");

            //Extrait les noms de colonnes et de tables
            List<string> fieldValuesList = new List<string>();
            List<string> fieldNamesList = new List<string>();
            foreach (IField field in entity.Fields.Values)
            {
                //On insert pas les champs de type système ni les foreigns
                //If (Not (TypeOf field Is IEntitySystemField)) And (Not (TypeOf field Is IForeignField)) Then
                if ((!(field is IForeignField)) & (!(field is IForeignFieldMultiTable)))
                {
                    if ((field is IEntitySystemField && updateSystemField))
                    {
                        ((IEntitySystemField)field).BeforeSave();
                    }
                    if (field is IEntityField)
                    {
                        if (!field.IsNull)
                        {
                            fieldValuesList.Add(this.FormatValue(field));
                            fieldNamesList.Add(((IEntityField)field).DatabaseFieldName);
                        }
                    }
                }
            }


            //Ajoute les colonnes au insert
            sb.Append(string.Join(",", fieldNamesList.ToArray()));
            //Ajoute les valeurs au Insert
            sb.Append(") VALUES(").Append(string.Join(",", fieldValuesList.ToArray())).Append(")");

            return sb.ToString();

        }

        /// <summary>
        /// Procédure de construction de la requête UPDATE
        /// </summary>
        /// <param name="entity">L'entité à updater</param>
        /// <returns>boolean</returns>
        /// <remarks></remarks>
        private string BuildInsertAttachedQuery(IBaseEntity entity, IBaseEntity parent, string linkSchemaName, string linkTableName)
        {
            string tableName = linkSchemaName + "." + linkTableName;
            StringBuilder sb = new StringBuilder();
            //Création du Insert
            sb.Append("INSERT INTO ").Append(tableName).Append(" (");

            //Ajoute les noms de colonnes
            sb.Append(((IEntityField)parent.GetPrimaryKeyField()).DatabaseFieldName).Append(", ");
            sb.Append(((IEntityField)entity.GetPrimaryKeyField()).DatabaseFieldName).Append(") ");

            //Ajoute les valeurs au Insert
            sb.Append(" VALUES(");
            sb.Append(this.FormatValue(parent.GetPrimaryKeyField())).Append(", ");
            sb.Append(this.FormatValue(entity.GetPrimaryKeyField())).Append(") ");

            return sb.ToString();

        }

        /// <summary>
        /// Procédure de construction de la requête UPDATE
        /// </summary>
        /// <param name="entity">L'entité à updater</param>
        /// <returns>boolean</returns>
        /// <remarks></remarks>
        private string BuildUpdateQuery(IBaseEntity entity, bool updateSystemField)
        {
            StringBuilder sb = new StringBuilder();

            //Extrait les noms de colonnes et de tables
            List<string> fieldValuesList = new List<string>();
            string tableName = ((IEntityField)entity.GetPrimaryKeyField()).Schema + "." + ((IEntityField)entity.GetPrimaryKeyField()).TableName;
            foreach (IField field in entity.Fields.Values)
            {
                if (!(field is IForeignField) & !(field is IForeignFieldMultiTable))
                {
                    if (field is IEntitySystemField && updateSystemField)
                    {
                        ((IEntitySystemField)field).BeforeSave();
                    }
                    if (field is IEntityField && field.IsDirty)
                    {
                        fieldValuesList.Add(((IEntityField)field).DatabaseFieldName + " = " + this.FormatValue(field));
                    }
                }
            }

            //Création du Update
            sb.Append("UPDATE ").Append(tableName).Append(" SET ");
            //Ajoute les colonnes au update
            sb.Append(string.Join(",", fieldValuesList.ToArray()));
            sb.Append(" WHERE ").Append(((IEntityField)entity.GetPrimaryKeyField()).DatabaseFieldName).Append(" = ").Append(this.FormatValue(entity.GetPrimaryKeyField()));

            return sb.ToString();

        }

        /// <summary>
        /// Fonction qui supprime un entité
        /// </summary>
        /// <param name="entity">L'entité à supprimer</param>
        /// <returns>Boolean</returns>
        /// <remarks></remarks>
        private string BuildDeleteQuery(IBaseEntity entity)
        {
            IField primaryKeyField = entity.GetPrimaryKeyField();
            StringBuilder sb = new StringBuilder();
            //Création du DELETE
            sb.Append("DELETE ").Append(((IEntityField)primaryKeyField).Schema + "." + ((IEntityField)primaryKeyField).TableName);
            sb.Append(" WHERE ").Append(((IEntityField)primaryKeyField).DatabaseFieldName).Append(" = ").Append(primaryKeyField.OriginalValue.ToString());

            return sb.ToString();

        }

        /// <summary>
        /// Fonction qui supprime un attachement
        /// </summary>
        /// <param name="entity">Entité attaché</param>
        /// <param name="parent">Parent</param>
        /// <param name="linkTableName">Nom de la table de lien</param>
        /// <returns>String</returns>
        /// <remarks></remarks>
        private string BuildDeleteAttachedQuery(IBaseEntity entity, IBaseEntity parent, string linkSchemaName, string linkTableName)
        {
            IField primaryKeyField = entity.GetPrimaryKeyField();
            StringBuilder sb = new StringBuilder();
            //Création du DELETE
            sb.Append("DELETE ").Append(linkSchemaName + "." + linkTableName);
            sb.Append(" WHERE ").Append(((IEntityField)primaryKeyField).DatabaseFieldName).Append(" = ").Append(primaryKeyField.OriginalValue.ToString());
            sb.Append(" AND ").Append(((IEntityField)parent.GetPrimaryKeyField()).DatabaseFieldName).Append(" = ").Append(parent.GetPrimaryKeyField().OriginalValue.ToString());

            return sb.ToString();

        }

        /// <summary>
        /// Fonction de formattage d'une valeur pour Oracle
        /// </summary>
        /// <param name="field"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        private string FormatValue(IField field)
        {
            string result = string.Empty;
            if (field.IsNull)
            {
                return "null";
            }
            //On format la valeur selon son type de donnée
            switch (field.FieldValueType.Name)
            {
                case "String":
                    result = "'" + field.Value.ToString().Replace("'", "''") + "'";
                    break;
                case "Integer":
                case "Int16":
                case "Int32":
                case "Int64":
                case "Short":
                case "Long":
                    result = field.Value.ToString();
                    break;
                case "Decimal":
                case "Single":
                case "Double":
                    result = field.Value.ToString().Replace(",", ".");
                    break;
                case "DateTime":
                    if (((DateTime)field.Value).Ticks <= DateTime.MinValue.Ticks)
                    {
                        result = "NULL";
                    }
                    else
                    {
                        result = "TO_DATE('" + ((DateTime)field.Value).ToString("yyyy-MM-dd HH:mm:ss") + "','YYYY-MM-DD HH24:MI:SS')";
                    }
                    break;
                case "Boolean":
                    result = Convert.ToInt32(field.Value).ToString();
                    break;
                case "Clob":
                    Int32 rest = field.Value.ToString().Length;
                    for (Int32 i = 0; i <= field.Value.ToString().Length / 4000; i++)
                    {
                        if (rest >= 4000)
                        {
                            result = result + " TO_CLOB('" + field.Value.ToString().Substring((i * 4000), 4000).Replace("'", "''") + "') || ";
                        }
                        else
                        {
                            result = result + " TO_CLOB('" + field.Value.ToString().Substring((i * 4000), rest).Replace("'", "''") + "')";
                        }
                        rest -= 4000;
                    }

                    break;
                default:
                    //Le type n'est pas supporté. Veuillez l'ajouter dans le Select Case
                    System.Diagnostics.Debug.Fail("Erreur dans la fonction OracleDataBroker.FormatValue(). Le type de donnée [" + field.Value.GetType().Name + "] n'est pas supporté.");
                    break;
            }
            return result;
        }

        /// <summary>
        /// Exécute une commande sql
        /// </summary>
        /// <param name="SQLCommandText">Command SQL</param>
        /// <param name="connection">Connexion bd</param>
        /// <returns>IDataReader</returns>
        /// <remarks></remarks>
        public System.Data.IDataReader ExecuteReader(string SQLCommandText, IDbConnection connection = null, Int32 fetchRowCount = 250)
        {
            IDataReader dataReader = null;
            OracleCommand command = null;

            if (connection == null)
            {
                command = new OracleCommand(SQLCommandText, (OracleConnection)this.Connection);
            }
            else
            {
                command = new OracleCommand(SQLCommandText, (OracleConnection)connection);
            }
            try
            {
                dataReader = command.ExecuteReader();
                if ((command.RowSize * fetchRowCount) > command.FetchSize)
                {
                    ((OracleDataReader)dataReader).FetchSize = command.RowSize * fetchRowCount;
                }

            }
            catch (OracleException ex)
            {
                this.HandleDBException(ex, null);
            }
            catch (Exception ex)
            {
            }

            return dataReader;
        }

        /// <summary>
        /// Ex.cure un commande
        /// </summary>
        /// <param name="command">Command</param>
        /// <returns>IDataReader</returns>
        /// <remarks></remarks>
        public IDataReader ExecuteReader(IDbCommand command, Int32 fetchRowCount = 250)
        {
            IDataReader dataReader = null;
            try
            {
                if (command.Connection == null || (command.Connection != null && command.Connection.State == ConnectionState.Closed))
                {
                    command.Connection = this.Connection;
                }
                dataReader = command.ExecuteReader();
                if (command is OracleCommand)
                {
                    if ((((OracleCommand)command).RowSize * fetchRowCount) > ((OracleCommand)command).FetchSize)
                    {
                        ((OracleDataReader)dataReader).FetchSize = ((OracleCommand)command).RowSize * fetchRowCount;
                    }
                }
            }
            catch (OracleException ex)
            {
                this.HandleDBException(ex, null);
            }

            return dataReader;
        }

        /// <summary>
        /// Procédure qui exécute une commande et ne retourne aucune valeur
        /// </summary>
        /// <param name="command">Comamnd à exécuter</param>
        /// <remarks></remarks>
        public Int32 ExecuteNonQuery(ref IDbCommand command)
        {
            Int32 result = 0;
            if (command.Connection == null || (command.Connection != null && command.Connection.State == ConnectionState.Closed))
            {
                command.Connection = this.Connection;
            }
            try
            {
                result = command.ExecuteNonQuery();
            }
            catch (OracleException ex)
            {
                this.HandleDBException(ex, null);
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                }
            }

            return result;
        }

        /// <summary>
        /// Fonction qui exécute une commande sql et ne retourne aucune valeur
        /// </summary>
        /// <param name="SQLCommandText">Command Sql</param>
        /// <param name="connection">Connection à la bd</param>
        /// <returns>Integer</returns>
        /// <remarks></remarks>
        public int ExecuteSQL(string SQLCommandText, IDbConnection connection = null)
        {
            Int32 result = 0;
            OracleCommand command = null;

            if (connection == null)
            {
                command = new OracleCommand(SQLCommandText, (OracleConnection)this.Connection);
            }
            else
            {
                command = new OracleCommand(SQLCommandText, (OracleConnection)connection);
            }
            try
            {
                result = command.ExecuteNonQuery();
            }
            catch (OracleException ex)
            {
                this.HandleDBException(ex, null);
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                }
            }

            return result;
        }

        /// <summary>
        /// Fonction qui exécute une command sql et retourne la première colonne de la première ligne
        /// </summary>
        /// <param name="SQLCommandText">Command sql</param>
        /// <param name="connection">Connection à la bd</param>
        /// <returns>Object</returns>
        /// <remarks></remarks>
        public object ExecuteScalar(string SQLCommandText, IDbConnection connection = null)
        {
            object result = null;
            OracleCommand command = null;

            if (connection == null)
            {
                command = new OracleCommand(SQLCommandText, (OracleConnection)this.Connection);
            }
            else
            {
                command = new OracleCommand(SQLCommandText, (OracleConnection)connection);
            }
            try
            {
                result = command.ExecuteScalar();
            }
            catch (OracleException ex)
            {
                this.HandleDBException(ex, null);
            }
            finally
            {
                if (command != null)
                {
                    command.Dispose();
                }
            }

            return result;
        }

        public IDbCommand CreateCommand(string SQLCommandText, System.Data.CommandType commandType)
        {
            OracleCommand command = new OracleCommand(SQLCommandText);
            command.CommandType = commandType;
            return command;
        }

        public DataSet Fill(string SQLCommandText, ref DataSet ds, IDbConnection connection = null)
        {
            OracleDataAdapter da = null;

            if (connection == null)
            {
            	da = new OracleDataAdapter(SQLCommandText, (OracleConnection)this.Connection);
            }
            else
            {
                da = new OracleDataAdapter(SQLCommandText, (OracleConnection)connection);
            }

            try
            {
                da.Fill(ds);
            }
            catch (OracleException ex)
            {
                this.HandleDBException(ex, null);
            }
            finally
            {
                if (da != null)
                {
                    da.Dispose();
                }
            }

            return ds;
        }

        public IDataParameter CreateParameter(string parameterName, eFieldType type, Int32 size, System.Data.ParameterDirection direction)
        {
            OracleParameter parameter = new OracleParameter();
            parameter.ParameterName = parameterName;
            parameter.Size = size;
            parameter.Direction = direction;

            switch (type)
            {
                case eFieldType.Boolean:
                    parameter.DbType = DbType.Boolean;
                    break;
                case eFieldType.DateTime:
                    parameter.DbType = DbType.DateTime;
                    break;
                case eFieldType.Decimal:
                    parameter.DbType = DbType.Decimal;
                    break;
                case eFieldType.Double:
                    parameter.DbType = DbType.Double;
                    break;
                case eFieldType.Integer:
                    parameter.DbType = DbType.Int32;
                    break;
                case eFieldType.Long:
                    parameter.DbType = DbType.Int64;
                    break;
                case eFieldType.Short:
                    parameter.DbType = DbType.Int16;
                    break;
                case eFieldType.Single:
                    parameter.DbType = DbType.Single;
                    break;
                case eFieldType.String:
                    parameter.DbType = DbType.String;
                    break;
            }

            return parameter;
        }

        /// <summary>
        /// Procédure qui traite les erreurs de la base de données.
        /// </summary>
        /// <param name="ex">L'exception bd</param>
        /// <param name="entity">L'entité qui a provoqué l'exception</param>
        /// <remarks></remarks>
        protected void HandleDBException(OracleException ex, IBaseEntity entity)
        {
            AppException exception = default(AppException);
            if (ex.Number == UNIQUE_CONSTRAINT)
            {
                if (entity != null)
                {
                    exception = new AppException("MSG_ENTITE_EXISTE_DEJA", ex);
                    exception.Parameters.Add(new AppExceptionParameter(((IEntityField)entity.GetPrimaryKeyField()).TableName, AppExceptionParameter.eParameterType.Localizable));
                    exception.Parameters.Add(new AppExceptionParameter(entity.ToString(), AppExceptionParameter.eParameterType.Value));
                    throw exception;
                }
            }
            else if (ex.Number == FOREIGN_CONSTRAINT)
            {
                if (entity != null)
                {
                    exception = new AppException("MSG_FOREIGN_INEXISTANT", ex);
                    exception.Parameters.Add(new AppExceptionParameter(((IEntityField)entity.GetPrimaryKeyField()).TableName, AppExceptionParameter.eParameterType.Localizable));
                    throw exception;
                }
            }
            else if (ex.Number == VALUE_TOO_LARGE)
            {
                if (entity != null)
                {
                    string fieldName = this.ExtractFieldNameFromException(ex.Message, "(\\.\"\\w+\"\\s)");
                    exception = new AppException("MSG_VALEUR_TROP_GRANDE", ex);
                    exception.Parameters.Add(new AppExceptionParameter(((IListField)entity.GetField(fieldName)).ListColumnName, AppExceptionParameter.eParameterType.Localizable));
                    throw exception;
                }
            }
            else if (ex.Number == VALUE_NUMERIC_TOO_LARGE)
            {
                exception = new AppException("MSG_VALEUR_NUMERIQUE_TROP_GRANDE", ex);
                throw exception;
            }
            else if (ex.Number == INVALID_NUMBER)
            {
                exception = new AppException("MSG_NOMBRE_INVALIDE", ex);
                throw exception;
            }
            else if ((ex.Number == NULL_EXCEPTION) | (ex.Number == NULL_EXCEPTION2))
            {
                if (entity != null)
                {
                    string fieldName = this.ExtractFieldNameFromException(ex.Message, "(\\.\"\\w+\"\\))");
                    exception = new AppException("MSG_CHAMP_REQUIS", ex);
                    exception.Parameters.Add(new AppExceptionParameter(((IListField)entity.GetField(fieldName)).ListColumnName, AppExceptionParameter.eParameterType.Localizable));
                    throw exception;
                }
            }
            else if ((ex.Number == INSUFFICIENT_PRIVILEGES) | (ex.Number == INSUFFICIENT_PRIVILEGES2))
            {
                exception = new AppException("MSG_ERREUR_PRIVILEGES_INSUFFISANTS", ex);
                throw exception;
            }
            else if ((ex.Number == INTEGRITY_CONSTRAINT))
            {
                exception = new AppException("MSG_ERREUR_CONTRAINTE_INTEGRITE", ex);
                throw exception;
            }
            else
            {
                throw new AppException("MSG_ERREUR_NON_GEREE", ex);
            }

            //on va traiter l'exception plus haut
            throw ex;
        }
        #endregion

    }
}